package com.zktr.dao;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import com.zktr.pojo.Goods;

public class GoodsExcelDao {
	   /**

     * 将Excel中的数据按顺序读取到一个集合中

     * @param 

     * @return

     */
		 public static void main(String[] args) throws Exception  {
			 enterGoods("F:/JSP2/.metadata/.plugins/org.eclipse.wst.server.core/tmp4/wtpwebapps/javaWebProjectJD/myFiles/pldrxkxxmb.xls");
		    }

	public static void enterGoods(String url) throws IOException, FileNotFoundException {
		GoodsExcelDao readExcelToDB = new GoodsExcelDao();
		 
		 List<Goods> insertDB = readExcelToDB.insertDB(url);
			System.out.println( insertDB);
			GoodsDao gDao = new GoodsDao();
			for (Goods adminRole : insertDB) {
				gDao.insertOneGoods(adminRole);
			}
	}

	public List<Goods> insertDB(String url) throws IOException, FileNotFoundException {
		File excelFile = new File(url);
		HSSFWorkbook hssfWorkbook = new HSSFWorkbook(new FileInputStream(excelFile));
		Goods goods = null;
		List<Goods> list = new ArrayList<Goods>();
		for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
			HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
			
			if (hssfSheet == null) {
				continue;
			}
		for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
			HSSFRow hssfRow = hssfSheet.getRow(rowNum);
			goods=new Goods();
			if (hssfRow == null) {
				continue;
			}
			HSSFCell one = hssfRow.getCell(0);
			if (one == null) {
				continue;
			}
			goods.setGoodsid((int)Double.parseDouble(getValue(one)));
			HSSFCell two = hssfRow.getCell(1);
			if (two == null) {
				continue;
			}
			goods.setCatid((int)Double.parseDouble(getValue(two)));
			HSSFCell three = hssfRow.getCell(2);
			if (three == null) {
				continue;
			}
			goods.setGoodssn(getValue(three));
			HSSFCell four = hssfRow.getCell(3);
			if (four == null) {
				continue;
			}
			goods.setGoodsname(getValue(four));
			HSSFCell cj = hssfRow.getCell(4);
			if (cj == null) {
				continue;
			}
			goods.setBrandid((int)Double.parseDouble(getValue(cj)));
			HSSFCell fave = hssfRow.getCell(5);
			if (fave == null) {
				continue;
			}
		    goods.setGoodsnumber((int)Double.parseDouble(getValue(fave)));
			HSSFCell six = hssfRow.getCell(6);
			if (six == null) {
				continue;
			}
			goods.setMarketprice(new BigDecimal(getValue(six)));
			HSSFCell seven = hssfRow.getCell(7);
			if (seven == null) {
				continue;
			}
			goods.setWarnnumber((int)Double.parseDouble(getValue(seven))); 
			HSSFCell eight = hssfRow.getCell(8);
			if (eight == null) {
				continue;
			}
	    	goods.setKeywords(getValue(eight));
			HSSFCell ten = hssfRow.getCell(9);
			if (ten == null) {
				continue;
			}
			goods.setGoodsbrief(getValue(ten));
			HSSFCell eleven  = hssfRow.getCell(10);
			if (eleven == null) {
				continue;
			}
			goods.setGoodsdesc(getValue(eleven)); 
			HSSFCell twelve = hssfRow.getCell(11);
			if (twelve == null) {
				continue;
			}
			goods.setGoodsimg(getValue(twelve));
			HSSFCell thirteen = hssfRow.getCell(12);
			if (thirteen == null) {
				continue;
			}
			goods.setIntegral((int)Double.parseDouble(getValue(thirteen)));
			HSSFCell fourteen = hssfRow.getCell(13);
			if (fourteen == null) {
				continue;
			}
			goods.setAddtime((int)Double.parseDouble(getValue(fourteen))); 
			HSSFCell fifteen = hssfRow.getCell(14);
			if (fifteen == null) {
				continue;
			}
			goods.setIsdelete(Boolean.parseBoolean(getValue(fifteen)));
			HSSFCell sixteen = hssfRow.getCell(15);
			if (sixteen == null) {
				continue;
			}
			goods.setIsnew(Boolean.parseBoolean(getValue(sixteen)));
			HSSFCell seventeen = hssfRow.getCell(16);
			if (seventeen == null) {
				continue;
			}
			goods.setIshot(Boolean.parseBoolean(getValue(seventeen))); 
			HSSFCell eighteen = hssfRow.getCell(17);
			if (eighteen == null) {
				continue;
			}
			goods.setTypeid((int)Double.parseDouble(getValue(eighteen)));
			HSSFCell nineteen = hssfRow.getCell(18);
			if (nineteen == null) {
				continue;
			}
			goods.setGiveintegral((int)Double.parseDouble(getValue(nineteen)));
		    list.add(goods);
		}
		}
	
		return list;
	}
	@SuppressWarnings({ "static-access" })
	private  String getValue(HSSFCell hssfCell) {
		if (hssfCell.getCellType() == hssfCell.CELL_TYPE_BOOLEAN) {
			return String.valueOf(hssfCell.getBooleanCellValue());
		} else if (hssfCell.getCellType() == hssfCell.CELL_TYPE_NUMERIC) {
			return String.valueOf(hssfCell.getNumericCellValue());
		} else {
			return String.valueOf(hssfCell.getStringCellValue());
		}
	}
}
